package com.eascs.datalist.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.eascs.admin.api.form.O2oAfterLoanForm;
import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.o2o.internal.util.StatusContexts;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.datalist.entity.O2oDataListEntity;
import com.eascs.notice.entity.OaDataListFileEntity;

/**
 * 
 * @author yuyong
 *
 */
@Repository
public class O2oDataListDao extends BaseO2oDao {
	
	public List<O2oDataListEntity> findDateEntity(String loanapplyNo,String type) {
		String sql="select * from o2o_datalist od where od.loanapply_no=? and od.datalist_code=? and od.is_delete=? ";
		return this.findEntitys(sql, O2oDataListEntity.class, loanapplyNo, type, StatusContexts.O2O_NO);
	}
	
	public List<O2oDataListEntity> findDateEntity(String loanapplyNo,String type, String paymentsId) {
		String sql="select * from o2o_datalist od where od.loanapply_no=? and od.datalist_code=? and od.payments_id=? and od.is_delete=? ";
		return this.findEntitys(sql, O2oDataListEntity.class, loanapplyNo, type, paymentsId, StatusContexts.O2O_NO);
	}
	
	public List<OaDataListFileEntity> findOaDateEntity(String loanapplyNo,String type) {
		String sql="select * from oa_datalist od where od.oa_bill_code=? and od.file_type=? and od.is_delete=? ";
		return this.findEntitys(sql, OaDataListFileEntity.class, loanapplyNo, type, StatusContexts.O2O_NO);
	}
	
	public O2oDataListEntity findCode(String loanapplyNo,String code){
		String sql="select * from o2o_datalist od where od.loanapply_no=? and od.datalist_code=? and  od.is_delete=?";
		return this.findEntity(sql, O2oDataListEntity.class, loanapplyNo,code,StatusContexts.O2O_NO);
	}
	
	public O2oDataListEntity findSponsorCode(String loanapplyNo,String code,String sponsorId){
		String sql="select * from o2o_datalist od where od.loanapply_no=? and od.datalist_code=? and  od.is_delete=? and od.sponsor_id=? ";
		return this.findEntity(sql, O2oDataListEntity.class, loanapplyNo,code,StatusContexts.O2O_NO,sponsorId);
	}
	
	public PageResult<Object[]> getCreditByPage(O2oAfterLoanForm entity){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getO2oIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT bill_code,t.creat_date, loan_user,operator_type, operator, document_no ,zxyh, ");
		querySql.append(" loan_type, bank_status, document_type, province,ltd_name ,operator_name,gdzt from ( ");
		querySql.append("SELECT da.datalist_no,da.creat_date,ly.bill_code,er.user_full_name as loan_user, '担保人' as operator_type, ");
		querySql.append("dbr.user_full_name as operator,dbr.document_no,ly.ltd,ly.bank_type,");
		querySql.append("(SELECT d.item_name FROM PUB_DDITEM d WHERE d.DD_NO = 10340 AND d.item_code = ly.bank_type ) AS zxyh,");
		querySql.append("(SELECT d.item_name FROM PUB_DDITEM d WHERE d.DD_NO = 11321 AND d.item_code = ly.loan_type ) AS  loan_type,");
		querySql.append("(SELECT d.item_name FROM pub_dditem_o2o d WHERE d.DD_NO = 1 AND  d.item_code = er.bank_status ) AS  bank_status, ");
		querySql.append(" (SELECT d.item_name FROM pub_dditem d WHERE d.DD_NO = 9981 AND  d.item_code = dbr.document_type) AS  document_type,");
		querySql.append("(SELECT pl.province_name from o2o_platform_regional pl where pl.platform_id=ly.ltd) AS  province,");
		querySql.append("(SELECT pl.province_code from o2o_platform_regional pl where pl.platform_id=ly.ltd) AS  province_code,");
		querySql.append("ly.ltd_name ,ly.operator_name,");
		querySql.append(" (SELECT d.item_name FROM pub_dditem_o2o d WHERE d.DD_NO = 'l46n7p91uggqq' AND  d.item_code = ly.is_place_on_file) as gdzt");
		querySql.append(" FROM o2o_datalist da ");
		querySql.append(" left JOIN o2o_loanapply ly ON da.loanapply_no = ly.loanapply_no");
		querySql.append(" left JOIN o2o_loan_user er ON ly.loanapply_no = er.loanapply_no");
		querySql.append(" AND ly.loan_id = er.loan_id and er.spouse_id is null and er.sponsor_type is null");
		querySql.append(" left join o2o_loan_user dbr  ON da.loanapply_no = dbr.loanapply_no AND da.sponsor_id = dbr.loan_id");
		querySql.append(" WHERE datalist_code ='SPONSOR_ZXBG' and is_delete='N' and dbr.sponsor_type='D' and ly.loan_type in ('BK','BP','BP_JX',,'SG''YC','SC')");
		querySql.append(" union ( SELECT da.datalist_no, da.creat_date, ly.bill_code, er.user_full_name AS loan_user, '借款人' AS operator_type, er.user_full_name AS operator, er.document_no, ly.ltd, ly.bank_type, ( SELECT d.item_name FROM PUB_DDITEM d WHERE d.DD_NO = 10340 AND d.item_code = ly.bank_type ) AS zxyh, ( SELECT d.item_name FROM PUB_DDITEM d WHERE d.DD_NO = 11321 AND d.item_code = ly.loan_type ) AS loan_type, ( SELECT d.item_name FROM pub_dditem_o2o d WHERE d.DD_NO = 1 AND d.item_code = er.bank_status ) AS bank_status, ( SELECT d.item_name FROM pub_dditem d WHERE d.DD_NO = 9981 AND d.item_code = er.document_type ) AS document_type, ( SELECT pl.province_name FROM o2o_platform_regional pl WHERE pl.platform_id = ly.ltd ) AS province, ( SELECT pl.province_code FROM o2o_platform_regional pl WHERE pl.platform_id = ly.ltd ) AS province_code, ly.ltd_name ,ly.operator_name, ( SELECT d.item_name FROM pub_dditem_o2o d WHERE d.DD_NO = 'l46n7p91uggqq' AND d.item_code = ly.is_place_on_file ) AS gdzt FROM o2o_datalist da LEFT JOIN o2o_loanapply ly ON da.loanapply_no = ly.loanapply_no LEFT JOIN o2o_loan_user er ON ly.loanapply_no = er.loanapply_no AND ly.loan_id = er.loan_id AND er.spouse_id IS NULL AND er.sponsor_type IS NULL WHERE datalist_code = 'ZXBGDATA' AND is_delete = 'N'and ly.loan_type in ('BK','BP','BP_JX','SG','YC','SC'))");
		querySql.append(" union (SELECT da.datalist_no, da.creat_date, ly.bill_code, er.user_full_name AS loan_user, '配偶' AS operator_type, pei.user_full_name AS operator, pei.document_no, ly.ltd, ly.bank_type, ( SELECT d.item_name FROM PUB_DDITEM d WHERE d.DD_NO = 10340 AND d.item_code = ly.bank_type ) AS zxyh, ( SELECT d.item_name FROM PUB_DDITEM d WHERE d.DD_NO = 11321 AND d.item_code = ly.loan_type ) AS loan_type, ( SELECT d.item_name FROM pub_dditem_o2o d WHERE d.DD_NO = 1 AND d.item_code = er.bank_status ) AS bank_status, ( SELECT d.item_name FROM pub_dditem d WHERE d.DD_NO = 9981 AND d.item_code = pei.document_type ) AS document_type, ( SELECT pl.province_name FROM o2o_platform_regional pl WHERE pl.platform_id = ly.ltd ) AS province, ( SELECT pl.province_code FROM o2o_platform_regional pl WHERE pl.platform_id = ly.ltd ) AS province_code, ly.ltd_name ,ly.operator_name, ( SELECT d.item_name FROM pub_dditem_o2o d WHERE d.DD_NO = 'l46n7p91uggqq' AND d.item_code = ly.is_place_on_file ) AS gdzt FROM o2o_datalist da LEFT JOIN o2o_loanapply ly ON da.loanapply_no = ly.loanapply_no LEFT JOIN o2o_loan_user er ON ly.loanapply_no = er.loanapply_no AND ly.loan_id = er.loan_id AND er.spouse_id IS NULL AND er.sponsor_type IS NULL LEFT JOIN o2o_loan_user pei ON da.loanapply_no = pei.loanapply_no AND pei.spouse_id = ly.loan_id  WHERE datalist_code = 'POZXBGDATA' AND is_delete = 'N'and ly.loan_type in ('BK','BP','BP_JX','SG','YC','SC'))");
		querySql.append(") t  ");
		
		querySql.append(" where /**DATA_PERMISSION(t.ltd)**/ 1=1  ");
		 
		
		
		if(!StringUtil.isEmpty(entity.getCreateDateStart())){
			querySql.append(" AND  t.creat_date >= ?");
			sqlPageQuery.addParam(entity.getCreateDateStart());
		}
		if(!StringUtil.isEmpty(entity.getCreateDateEnd())){
			querySql.append(" AND t.creat_date < ?");
			sqlPageQuery.addParam(DateUtils.formateStandDate(DateUtils.addDate(DateUtils.formateStandDate(entity.getCreateDateEnd()), 1)));
		}
		
		if(!StringUtil.isEmpty(entity.getCoopereBank())){
			querySql.append(" AND t.bank_type = ?");
			sqlPageQuery.addParam(entity.getCoopereBank());
		}
		if(!StringUtil.isEmpty(entity.getProvince())){
			querySql.append(" AND t.province_code  = ?");
			sqlPageQuery.addParam(entity.getProvince());
		}
		
		if(!StringUtil.isEmpty(entity.getBillCode())){
			querySql.append(" AND t.bill_code = ?");
			sqlPageQuery.addParam(entity.getBillCode());
		}
		
		querySql.append(" ORDER BY creat_date desc ");
  		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	
	}
}
